package work.javac.exec;

import work.javac.bean.Table;
import work.javac.common.ConfigUtil;
import work.javac.common.FileReadWriter;
import work.javac.common.Log;
import work.javac.common.PathUtil;
import work.javac.common.database.SQLExecute;
import work.javac.common.database.utils.DBUtils;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

public class Create {

    private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
    private static final String EOL = System.getProperty("line.separator");
    private static final String INIT = "create_";
    private static final String TYPE = ".sql";
    private static StringBuffer sql = new StringBuffer(100000);

    public static void start(Map map) throws SQLException, IOException {
        String output = (String) map.get("output");
        String tab = (String) map.get("table");
        if(output == null){
            Log.err("请检查参数(-o)输出数据源不能为空!");
            System.exit(1);
        }
        Log.info("开始生成创建脚本");
        String tableSQL;
        try {
            tableSQL = gsTable(output, tab);
        }catch (SQLException | RuntimeException e){
            tableSQL = table(output, tab);
        }
        LocalDateTime localDateTime = LocalDateTime.now();
        String path = PathUtil.getFile(INIT + localDateTime.format(formatter) + TYPE).getPath();
        FileReadWriter.writer(path, tableSQL);
        Log.info("表结构创建脚本生成完毕!路径:%s;请手动确认后在数据库执行!", path);
    }

    public static String  gsTable(String output, String table) throws SQLException{
        try (Connection conn = DBUtils.getConn(output)){
            List<String> tableDef = SQLExecute.getTableDef(conn, table);
            tableDef.forEach(item -> {
                sql.append(item);
                sql.append(EOL);
                sql.append(EOL);
            });
            if("1".equals(ConfigUtil.get("create.view"))){
                String views = SQLExecute.getViews(conn);
                sql.append(views);
            }
//            DBUtils.close(conn);
            return sql.toString();
        }
    }

    public static String table(String output, String tab) throws SQLException {
        try (Connection conn = DBUtils.getConn(output)){

            List<Table> tables = SQLExecute.getTables(conn, tab);
            String orientation = ConfigUtil.get("create.orientation");
            String compression = "COLUMN".equalsIgnoreCase(orientation) ? ConfigUtil.get("create.compression") : "NO";
            tables.forEach(table -> {
                //删除表
                sql.append("DROP TABLE IF EXISTS ");
                sql.append(table.getTableName());
                sql.append(";");
                sql.append(EOL);

                //创建表
                sql.append("CREATE TABLE IF NOT EXISTS ");
                sql.append(table.getTableName());
                sql.append("(");
                sql.append(EOL);

                //表字段
                String columnName = table.getColumns().get(table.getColumns().size() - 1).getColumnName();
                table.getColumns().forEach(column -> {
                    sql.append("\t\t");
                    sql.append(column.getColumnName());
                    sql.append("\t");
                    sql.append(column.getTypeName());
                    if(!column.getTypeName().toUpperCase().matches("CLOB|DATE|TIME.+") && column.getColumnSize() != 0){
                        sql.append("(");
                        if(column.getTypeName().toUpperCase().matches("VARCHAR.+")){
                            sql.append(
                                    BigDecimal.valueOf(
                                            Math.ceil(
                                                    column.getColumnSize() * Double.parseDouble(ConfigUtil.get("create.varchar"))
                                            )
                                    ).stripTrailingZeros().toPlainString()
                            );
                        }else{
                            sql.append(column.getColumnSize());
                        }
                        if(column.getDecimalDigits() != 0){
                            sql.append(",");
                            sql.append(column.getDecimalDigits());
                        }
                        sql.append(")");
                    }
                    if(column.getNullable() == 0 || "1".equals(ConfigUtil.get("create.notnull"))){
                        sql.append(" NOT NULL");
                    }
                    if(column.getColumnDef() != null){
                        sql.append(" DEFAULT ");
                        sql.append(column.getColumnDef());
                    }
                    if (!columnName.equals(column.getColumnName()) || table.getPkName() != null){
                        sql.append(",");
                    }
                    if(column.getRemarks() != null){
                        sql.append(" /* ");
                        sql.append(column.getRemarks());
                        sql.append(" */");
                    }
                    sql.append(EOL);
                });
                //表主键
                if(table.getPkName() != null){
                    sql.append("\t\t");
                    sql.append("CONSTRAINT ");
                    sql.append(table.getPkName());
                    sql.append(" PRIMARY KEY (");
                    table.getPrimaryKeys().forEach(item -> {
                        sql.append(item);
                        sql.append(", ");
                    });
                    sql.delete(sql.length() - ", ".length(), sql.length());
                    sql.append(")");
                    sql.append(EOL);
                }
                sql.append(")");
                sql.append(" WITH ");
                sql.append("(ORIENTATION = ");
                sql.append(orientation);
                sql.append(", COMPRESSION = ");
                sql.append(compression);
                sql.append(")");

                if("1".equals(ConfigUtil.get("create.distribute"))){
                    sql.append(EOL);
                    sql.append("DISTRIBUTE BY HASH(");
                    AtomicInteger i = new AtomicInteger();
                    if(table.getPkName() != null){
                        table.getPrimaryKeys().forEach(item -> {
                            sql.append(item);
                            sql.append(", ");
                        });
                        sql.delete(sql.length() - ", ".length(), sql.length());
                        sql.append(")");
                        sql.append("  /* 该表存在主键,分布列由主键构成! */");
                    }else{
                        table.getColumns().forEach(item -> {
                            if(i.get() == 2){
                                return;
                            }
                            String colName = item.getColumnName().toUpperCase();
                            if(colName.matches("TICD|CSNM|CTAC|ELEMTYPE|.+KEY|.+\\_ID|.+\\_NUM|.+CODE|CST.+")){
                                sql.append(colName);
                                sql.append(", ");
                                i.getAndIncrement();
                            }
                        });
                        if(i.get() == 0){
                            sql.append(")");
                            sql.append("  /* 找不到合适的分布列,请人工分析选择! */");
                        }else{
                            sql.delete(sql.length() - ", ".length(), sql.length());
                            sql.append(")");
                            sql.append("  /* 分布列由推荐的值构成,建议再次确认! */");
                        }
                    }
                }

                //表分区
                if(table.getPartitions() != null){
                    sql.append(EOL);
                    sql.append("PARTITION BY RANGE (");
                    sql.append(table.getPartitions().get(0).getColumnName());
                    sql.append(")");
                    sql.append(EOL);
                    sql.append("(");
                    sql.append(EOL);
                    table.getPartitions().forEach(item -> {
                        sql.append("\t\t");
                        sql.append("PARTITION ");
                        sql.append(item.getPartitionName());
                        sql.append(" VALUES LESS THAN ('");
                        sql.append(item.getPartitionValueLessThan());
                        sql.append("') ,");
                        sql.append(EOL);
                    });
                    sql.delete(sql.length() - EOL.length() - 1, sql.length());
                    sql.append(EOL);
                    sql.append(")");
                    sql.append(EOL);
                    sql.append("ENABLE ROW MOVEMENT");
                }

                sql.append(";");
                sql.append(EOL);
                sql.append(EOL);

                //创建索引
                if("1".equals(ConfigUtil.get("create.index"))){
                    table.getIndexs().forEach(item -> {
                        sql.append("CREATE ");
                        if(item.isNonUnique()){
                            sql.append(" UNIQUE ");
                        }
                        sql.append(" INDEX ");
                        sql.append(item.getIndexName());
                        sql.append(" ON ");
                        sql.append(table.getTableName());
                        sql.append(" (");
                        item.getColumns().forEach(col -> sql.append(col).append(","));
                        sql.deleteCharAt(sql.length() - 1);
                        sql.append(")");
                        if(table.getPartitions() != null){
                            sql.append(" LOCAL");
                        }
                        sql.append(";");
                        sql.append(EOL);
                    });
                    sql.append(EOL);
                }

                //表注释
                if(table.getRemarks() != null){
                    sql.append("COMMENT ON TABLE ");
                    sql.append(table.getTableName());
                    sql.append(" IS '");
                    sql.append(table.getRemarks());
                    sql.append("';");
                    sql.append(EOL);
                }

                //字段注释
                table.getColumns().forEach(column -> {
                    if(column.getRemarks() != null){
                        sql.append("COMMENT ON COLUMN ");
                        sql.append(table.getTableName());
                        sql.append(".");
                        sql.append(column.getColumnName());
                        sql.append(" IS '");
                        sql.append(column.getRemarks());
                        sql.append("';");
                        sql.append(EOL);
                    }
                });

                sql.append(EOL);
                sql.append(EOL);
            });

            if("1".equals(ConfigUtil.get("create.view"))){
                String views = SQLExecute.getViews(conn);
                sql.append(views);
            }
//            DBUtils.close(conn);
            return sql.toString();
        }
    }

}
